package com.ujiuye.dao;

import com.ujiuye.pojo.Book;
import com.ujiuye.utils.C3p0Utils;
import com.ujiuye.utils.PageTool;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class BookDao {
    QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());


    public List<Book> lookAllBooks(String num) {
        List<Book> list = null;
        StringBuffer sql = new StringBuffer();
        //String sql = "select * from book";
        sql.append("select * from book ");
        if (num.equals("1")){
           sql.append(" order by bid desc ");
        }
        //String sql = "select * from student limit ?,?";
        try {
            list = queryRunner.query(sql.toString(), new BeanListHandler<Book>(Book.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public int addBook(Book book) {
        int i =0;
        String sql ="INSERT INTO book VALUES(null,?,?,?,?) ";
        Object[] params = {book.getBname(),book.getBprice(),book.getBauthor(),book.getBdesc()};
        try {
            i = queryRunner.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    public Book lookBookByBid(String bid) {
        String sql ="select * from book where bid=?";
        Book query = null;
        try {
            query = queryRunner.query(sql, new BeanHandler<Book>(Book.class), bid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    public int upDateBook(Book book) {
        int i =0;
        String sql = "UPDATE book SET bname=?,bprice=?,bauthor=?,bdesc=? WHERE bid=?";
        Object[] params = {book.getBname(),book.getBprice(),book.getBauthor(),book.getBdesc(),book.getBid()};
        try {
            i = queryRunner.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    public int deleteBookByBid(String bid) {
        int i =0;
        String sql="delete from book where bid=?";
        try {
            i = queryRunner.update(sql, bid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    public int lookCounts() {
        String sql = "select count(*) from book";
        Long query =null;
        try {
             query = (Long) queryRunner.query(sql, new ScalarHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query.intValue();
    }
}
